USE [BMIG_MENSUAL_POST_CADENA]

 -- creating the store procedure
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = 'WASP_M0010_DESCRIPCIONES'
	   AND 	  type = 'P') 
	DROP PROCEDURE WASP_M0010_DESCRIPCIONES
GO

CREATE PROCEDURE [dbo].[WASP_M0010_DESCRIPCIONES]AS
begin 
    SELECT DISTINCT 
         a.TipoRegistro
       , CAST(a.PeriodoPro AS varchar) AS PeriodoPro
       , a.FechaEnvio
       , CAST(a.NroCorrelativoArchivo AS varchar) AS NroCorrelativoArchivo
       , CAST(a.TipoMovimiento AS varchar) AS TipoMovimiento
       , CAST(a.NroProducto AS varchar) AS NroProducto
       , CAST(a.NroPoliza AS varchar) AS NroPoliza
       , a.CODAGEN
       , CAST(a.NroCredito AS varchar) AS NroCredito
       , CAST(a.RutAsegurado AS varchar) AS RutAsegurado
       , a.[DV Rut Asegurado]
       , a.[Nombre Asegurado]
       , a.DireccionAsegurado
       , CAST(a.TelefonoAsegurado AS varchar) AS TelefonoAsegurado
       , CAST(a.CodigoComuna AS varchar)AS CodigoComuna
       , b.z07310DSC AS DescripcionComuna
       , CAST(a.CodigoCiudad AS varchar) AS CodigoCiudad
       , CAST(a.DescripcionCuidad AS varchar)AS DescripcionCuidad
       , CAST(a.CodigoRegion AS varchar) AS CodigoRegion
       , c.z07339DSC AS DescripcionRegion
       , a.VigenciaDesde
       , a.VigenciaHasta
       , CAST(d.totcuotas AS varchar) AS CuotasPactadas
       , NULL AS PrimaNetaCaja
       , CASE 
              WHEN (d .totcuotas BETWEEN 3 AND 24) THEN (0.6700) 
              WHEN (d .totcuotas BETWEEN 25 AND 36) THEN (1.2100) 
              WHEN (d .totcuotas BETWEEN 37 AND 60) THEN (1.7100) 
              ELSE 0 
         END AS PrimaBrutaCaja
       , a.PrimaNetaPesos
       , a.PrimaBrutaPesos
    into	dbo.ing_produccion   
    FROM  dbo.ing_produccion_temp AS a 
          LEFT OUTER JOIN dbo.z07310 AS b WITH (nolock) ON a.codigocomuna = b.z07310COM 
          LEFT OUTER JOIN dbo.z07339 AS c WITH (nolock) ON a.CodigoRegion = c.z07339REG 
          CROSS JOIN dbo.Produccion_Cuotas AS d
    WHERE (a.NroCredito = d.ppoper) AND (a.RutAsegurado = d.ppcta) AND (a.CODAGEN = d.ppsuc)
END    


